﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Data.OleDb;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection pharmolapdw;
        private SqlConnection competitors;
        private DataTable[] dt_rep;
        private delegate void Deleg();
        private Deleg del;
        private Guid guid;
        private DataTable begdata = new DataTable();
        private DataTable dt_pharm;
        private DataTable dt_maks;
        private DataTable pricesel;
        private struct sp_params
        {
            public string name;
            public object value;
        }
        private string par = "";
        private string site = "";

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }
        private int websiteid = 0;

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                pharmolapdw = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                competitors = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=Competitors;Integrated Security=True");
                dt_rep = new DataTable[1];
                dt_rep[0] = new DataTable();
                guid = Guid.NewGuid();
                dateTimePicker1.Value = DateTime.Now.AddDays(-1);
                dateTimePicker2.Value = DateTime.Now.AddDays(-1);

                DataTable[] dt_site = SqlProcExecWithQuery(competitors, "pas_WebSiteSel", 300, null);
                NewItem[] newitem = new NewItem[0];

                foreach (DataRow row in dt_site[0].Rows)
                {
                    Array.Resize(ref newitem, newitem.Length + 1);
                    newitem[newitem.Length - 1] = new NewItem();
                    newitem[newitem.Length - 1].ParamDirectoryID = Convert.ToInt32(row[0]);
                    newitem[newitem.Length - 1].ParamComment = row[1].ToString();
                    comboBox1.Items.Add(newitem[newitem.Length - 1]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private DataTable[] SqlProcExecWithQuery(SqlConnection con, string procname, int timeout, sp_params[] param)
        {
            DataTable[] dt = new DataTable[0];
            try
            {
                if (con.State == ConnectionState.Closed) con.Open();
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.StoredProcedure;
                com.CommandTimeout = timeout;
                com.CommandText = procname;

                if (param != null)
                {
                    foreach (sp_params p in param)
                    {
                        com.Parameters.AddWithValue(p.name, p.value);
                    }
                }
                SqlDataReader dr = com.ExecuteReader();

                while (dr.HasRows)
                {
                    Array.Resize(ref dt, dt.Length + 1);
                    dt[dt.Length - 1] = new DataTable();
                    dt[dt.Length - 1].Load(dr);
                    if (dt[dt.Length - 1].Columns.Count > 2) dt[dt.Length - 1].TableName = dt[dt.Length - 1].Rows[0][1].ToString();
                    if (dr.IsClosed) break;
                }

                dr.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep = null;//.Clear();
            dt_rep = new DataTable[1];
            dt_rep[0] = new DataTable();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep[0].DefaultView));
        }

        private void CreateRep()
        {
            if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
            try
            {
                guid = Guid.NewGuid();

                SqlCommand myCommand = pharmolapdw.CreateCommand();
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = @"pas_GuidNatureCodeINS";
                myCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
                myCommand.Parameters.Add("@code", SqlDbType.Int);
                myCommand.Parameters.Add("@del", SqlDbType.Bit);
                myCommand.Parameters.Add("@nature", SqlDbType.VarChar, 5);
                myCommand.Parameters.Add("@naturecomm", SqlDbType.VarChar, 100);

                if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
                int fl_ph = 0;
                int pharm = 0;
                if (dt_pharm != null)
                {
                    foreach (DataRow dr in dt_pharm.Rows)
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                        {
                            pharm = Convert.ToInt32(dr["Код"]);
                            fl_ph++;
                        }
                }

                int fl_m = 0;
                if (dt_maks != null)
                {
                    foreach (DataRow dr in dt_maks.Rows)
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                        {
                            myCommand.Parameters["@guid"].Value = guid;
                            myCommand.Parameters["@code"].Value = 0;
                            myCommand.Parameters["@del"].Value = false;
                            myCommand.Parameters["@nature"].Value = "maksa";
                            myCommand.Parameters["@naturecomm"].Value = dr[1].ToString() + "^^" + dr[2].ToString() + ", " + dr[3].ToString();
                            myCommand.ExecuteNonQuery();
                            fl_m++;
                        }
                }

                if (fl_ph == 1 && fl_m > 0)
                {
                    dt_rep = null;
                    dt_rep = new DataTable[1];
                    dt_rep[0] = new DataTable();
                    dt_rep[0].Load(RepProc(guid, pharmolapdw, dateTimePicker1, pharm, dateTimePicker2));
                }
                else
                {
                    if ((fl_ph == 0 || fl_ph > 1) && fl_m == 0)
                        MessageBox.Show("Выберите ОДНУ аптеку ФА и аптеки конкурентов");
                    else
                    {
                        if (fl_ph == 0 || fl_ph > 1) MessageBox.Show("Выберите ОДНУ аптеку ФА");
                        if (fl_m == 0)MessageBox.Show("Выберите аптеки конкурентов");
                    }
                }

                myCommand.Parameters["@guid"].Value = guid;
                myCommand.Parameters["@code"].Value = null;
                myCommand.Parameters["@del"].Value = true;
                myCommand.Parameters["@nature"].Value = null;
                myCommand.Parameters["@naturecomm"].Value = null;
                myCommand.ExecuteNonQuery();

                pharmolapdw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                pharmolapdw.Close();
            }

        }

        private SqlDataReader RepProc(Guid id, SqlConnection sqlcon, DateTimePicker dtp, int pharm, DateTimePicker dtp1)
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"pas_DAC_MaksavitRepor";
            sqlcom.CommandTimeout = 3000;

            sqlcom.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlcom.Parameters["@guid"].Value = id;

            sqlcom.Parameters.Add("@date", SqlDbType.Date);
            sqlcom.Parameters["@date"].Value = dtp.Value.Date;

            sqlcom.Parameters.Add("@datebal", SqlDbType.Date);
            sqlcom.Parameters["@datebal"].Value = dtp1.Value.Date;

            sqlcom.Parameters.Add("@pharm", SqlDbType.Int);
            sqlcom.Parameters["@pharm"].Value = pharm;

            sqlcom.Parameters.Add("@wsid", SqlDbType.Int);
            sqlcom.Parameters["@wsid"].Value = websiteid;

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "максавит";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            begdata.Clear();
            OpenFileDialog opf = new OpenFileDialog();
            opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
            if (opf.ShowDialog() == DialogResult.OK)
            {

                try
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { }); //{ null, null, null, null }
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    //object[] obj = new object[] { sheet1 , "CStr("};
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    begdata = ds.Tables[0];
                    if (begdata.Columns.Count > 0)
                    {
                        ////DataColumn flag = begdata.Columns.Add("Флаг", typeof(Boolean));
                        ////flag.ReadOnly = false;
                        ////begdata.Columns["Флаг"].SetOrdinal(0);
                        //foreach (DataRow dr in begdata.Rows)
                        //{
                        //    //dr[0] = true;
                        //    //dr[2] = dr[2].ToString().Replace(" ", "");
                        //}
                        //foreach (DataColumn dc in begdata.Columns)
                        //{
                        //    //if (dc.Ordinal != 0) dc.ReadOnly = true;
                        //}
                        cn.Close();
                        //DragDropDGV1();
                        dataGridView1.DataSource = null;
                        dataGridView1.DataSource = begdata.DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //try
            //{
            //    SqlCommand sp; 
            //    if (begdata.Rows.Count > 0)
            //    {
            //        sp = conn.CreateCommand();
            //        sp.CommandText = "pas_MaksavitPriceInsDel";
            //        sp.CommandType = CommandType.StoredProcedure;
            //        sp.Parameters.AddWithValue("@maks", "");
            //        sp.Parameters.AddWithValue("@prod", "");
            //        sp.Parameters.AddWithValue("@dateupd", "");
            //        sp.Parameters.AddWithValue("@price", "");
            //        sp.Parameters.AddWithValue("@bal", "");
            //        sp.Parameters.AddWithValue("@city", "");
            //        sp.Parameters.AddWithValue("@pharm", "");
            //        sp.Parameters.AddWithValue("@del", 0);

            //        foreach (DataRow dr in begdata.Rows)
            //        {
            //            sp.Parameters["@maks"].Value = dr[0];
            //            sp.Parameters["@prod"].Value = dr[1];
            //            sp.Parameters["@dateupd"].Value = dr[2];
            //            sp.Parameters["@price"].Value = dr[3];
            //            sp.Parameters["@bal"].Value = dr[4];
            //            sp.Parameters["@city"].Value = dr[5];
            //            sp.Parameters["@pharm"].Value = dr[6];
            //            if (conn.State == ConnectionState.Closed) conn.Open();
            //            sp.ExecuteNonQuery();
            //        }
            //        begdata.Clear();
            //        MessageBox.Show("Данные таблицы добавлены");
            //    }
            //    sp = null;
            //    sp = conn.CreateCommand();
            //    sp.CommandText = "pas_DAC_MaksavitPriceSel";
            //    sp.CommandType = CommandType.StoredProcedure;
            //    sp.Parameters.AddWithValue("@date", dateTimePicker1.Value.Date);
            //    sp.Parameters.AddWithValue("@guid", DBNull.Value);

            //    pricesel = null;
            //    pricesel = new DataTable();
            //    dataGridView1.DataSource = null;
            //    if (conn.State == ConnectionState.Closed) conn.Open();
            //    pricesel.Load(sp.ExecuteReader());

            //    dataGridView1.DataSource = pricesel.DefaultView;
            //    dt_maks = null;
            //    listBox2.Items.Clear();

            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());
            //}
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (dt_pharm == null)
                {
                    SqlCommand myCommand = pharmolapdw.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_PharmSel";
                    if (pharmolapdw.State == ConnectionState.Closed) pharmolapdw.Open();
                    dt_pharm = new DataTable();
                    dt_pharm.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_pharm.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    pharmolapdw.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                textBox2.Text = "";
                int fl = 0;
                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        fl++;
                        if (fl > 1) break;
                        textBox2.Text = dr[f3.sortcoumn].ToString();
                    }
                }
                if(fl > 1 || fl == 0) 
                {
                    MessageBox.Show("Выберите ОДНУ аптеку ФА");
                    button3.Focus();
                    textBox2.Text = "Выберите одну аптеку";
                }
                f3.Close();
                f3 = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox1.SelectedIndex > -1)
                {
                    PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                    if (dt_maks == null)
                    {
                        SqlCommand myCommand = competitors.CreateCommand();
                        myCommand.CommandType = CommandType.StoredProcedure;
                        myCommand.CommandText = @"pas_CompPharmSel";
                        myCommand.Parameters.AddWithValue("@wsid", (comboBox1.SelectedItem as NewItem).ParamDirectoryID);
                        if (competitors.State == ConnectionState.Closed) competitors.Open();
                        dt_maks = new DataTable();
                        dt_maks.Load(myCommand.ExecuteReader());
                        foreach (DataColumn dc in dt_maks.Columns)
                        {
                            if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                            else dc.ReadOnly = false;
                        }
                        competitors.Close();
                    }
                    f3.namechcol = namechcol;
                    f3.Text = "Фильтр по аптекам конкурентов";
                    f3.sortcoumn = "Аптека";
                    f3.dataGridView1.DataSource = dt_maks.DefaultView;
                    f3.ShowDialog();
                    listBox2.Items.Clear();
                    foreach (DataRow dr in dt_maks.Rows)
                    {
                        if (Convert.ToBoolean(dr[namechcol]) == true)
                            listBox2.Items.Add(dr[f3.sortcoumn] + "(" + dr[2] + ", " + dr[3] + ")");
                    }
                    if (listBox2.Items.Count == 0)
                    {
                        listBox2.Items.Add("Выберите аптеки конкурентов");
                    }
                    f3.Close();
                    f3 = null;
                }
                else MessageBox.Show("Выберите сайт");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            dt_maks = null;
            listBox2.Items.Clear();
            websiteid = (comboBox1.SelectedItem as NewItem).ParamDirectoryID;
            //if ((sender as ComboBox).SelectedIndex != -1)
            //{
            //    try
            //    {
            //        DataTable[] dt_rep1 = SiteParamsSel(competitors, (comboBox1.SelectedItem as NewItem).ParamDirectoryID);

            //        Control[] control = new Control[0];

            //        for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
            //        {
            //            if (comboBox1.Parent.Controls[i].Name != "comboBox1"
            //                && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
            //            {
            //                Array.Resize(ref control, control.Length + 1);
            //                control[control.Length - 1] = comboBox1.Parent.Controls[i];
            //            }
            //        }
            //        foreach (Control c in control)
            //        {
            //            comboBox1.Parent.Controls.Remove(c);
            //        }
            //        ComboBoxAddItems(dt_rep1);
            //        int countcb = 0;
            //        for (int i = 0; i < comboBox1.Parent.Controls.Count; i++)
            //        {
            //            if (comboBox1.Parent.Controls[i].Name != "comboBox1"
            //                && comboBox1.Parent.Controls[i].GetType().ToString() == @"System.Windows.Forms.ComboBox")
            //                countcb++;
            //        }
            //        //dataGridView1.DataSource = null;
            //        //dt_drugs = null;
            //        if (countcb == 0)
            //        {
            //         //   dt_drugs = DrugsSel((comboBox1.SelectedItem as NewItem).ParamDirectoryID, null);
            //         //   dataGridView1.DataSource = dt_drugs.DefaultView;
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //    }
            //}
        }

        private DataTable[] SiteParamsSel(SqlConnection sqlcon, int value)
        {
            try
            {
                sp_params[] par = new sp_params[1];
                par[0].name = "@wsid";
                par[0].value = value;
                DataTable[] dt = SqlProcExecWithQuery(sqlcon, "pas_ParamsSel", 300, par);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private void ComboBoxAddItems(DataTable[] dt)
        {
            try
            {
                DataRow r;
                DataTable tmp = new DataTable();
                Point point = new Point(comboBox1.Location.X, comboBox1.Location.Y + comboBox1.Height + 10);
                try
                {
                    foreach (DataTable table in dt)
                    {
                        if (table.Columns.Count == 2)
                        {
                            site = table.Rows[0][0].ToString();
                            par = table.Rows[0][1].ToString();
                        }
                        if (table.Columns.Count > 2)
                        {
                            if (!Convert.ToBoolean(table.Rows[0][2]))
                            {

                                ComboBox cb = ComboBoxAdd(point, table.Rows[0][1].ToString(), groupBox3,comboBox1.Height,comboBox1.Width);
                                point.Y += comboBox1.Height + 10;
                                NewItem[] newitem = new NewItem[0];

                                foreach (DataRow row in table.Rows)
                                {
                                    r = row;
                                    Array.Resize(ref newitem, newitem.Length + 1);
                                    newitem[newitem.Length - 1] = new NewItem();
                                    newitem[newitem.Length - 1].ParamDirectoryID = Convert.ToInt32(row[0]);
                                    newitem[newitem.Length - 1].ParamName = row[1].ToString();
                                    newitem[newitem.Length - 1].AnyValue = Convert.ToBoolean(row[2]);
                                    newitem[newitem.Length - 1].ParamValue = row[3].ToString();
                                    newitem[newitem.Length - 1].ParamComment = row[4].ToString();
                                    if (row[5] != DBNull.Value && row[5].ToString() != "" && row[5].ToString() != " ") newitem[newitem.Length - 1].ParentID = Convert.ToInt32(row[5]);
                                    newitem[newitem.Length - 1].Worked = false;
                                    cb.Items.Add(newitem[newitem.Length - 1]);
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                foreach (DataTable table in dt)
                {
                    if (table.Columns.Count > 2)
                    {
                        if (Convert.ToBoolean(table.Rows[0][2]))
                        {
                            NewItem[] newitem = new NewItem[0];
                            foreach (DataRow row in table.Rows)
                            {
                                Array.Resize(ref newitem, newitem.Length + 1);
                                newitem[newitem.Length - 1] = new NewItem();
                                newitem[newitem.Length - 1].ParamDirectoryID = Convert.ToInt32(row[0]);
                                newitem[newitem.Length - 1].ParamName = row[1].ToString();
                                newitem[newitem.Length - 1].AnyValue = Convert.ToBoolean(row[2]);
                                newitem[newitem.Length - 1].ParamValue = row[3].ToString();
                                newitem[newitem.Length - 1].ParamComment = row[4].ToString();
                                if (row[5] != DBNull.Value && row[5].ToString() != "" && row[5].ToString() != " ") newitem[newitem.Length - 1].ParentID = Convert.ToInt32(row[5]);
                                newitem[newitem.Length - 1].Worked = false;
                                bool fl = false;
                                foreach (Control contrl in comboBox1.Parent.Controls)
                                {
                                    if (contrl.GetType().ToString() == @"System.Windows.Forms.ComboBox"
                                        && contrl.Name != "comboBox1")
                                    {
                                        for (int i = 0; i < (contrl as ComboBox).Items.Count; i++)
                                        {
                                            NewItem ni = (contrl as ComboBox).Items[i] as NewItem;
                                            NewItem[] child;
                                            if (ni.children == null) child = new NewItem[0];
                                            else child = ni.children;
                                            if (ni.ParamDirectoryID == newitem[newitem.Length - 1].ParentID)
                                            {
                                                Array.Resize(ref child, child.Length + 1);
                                                child[child.Length - 1] = newitem[newitem.Length - 1];
                                                ((contrl as ComboBox).Items[i] as NewItem).children = child;
                                                fl = true;
                                                break;
                                            }
                                        }
                                    }
                                    if (fl) break;
                                }
                            }
                        }
                    }
                }
                //comboBox1.Parent.Parent.Height = point.Y + 10;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private ComboBox ComboBoxAdd(Point location, string name, GroupBox gb,int height,int width)
        {
            ComboBox cb = new ComboBox();
            cb.Name = name;
            cb.Location = location;
            cb.Height = height;
            cb.Width = width;
            cb.Visible = true;
            gb.Controls.Add(cb);

            return cb;
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            try
            {
                if (comboBox1.SelectedIndex > -1)
                {
                    dataGridView1.DataSource = null;
                    sp_params[] par = new sp_params[3];
                    par[0] = Value("@wsid", (comboBox1.SelectedItem as NewItem).ParamDirectoryID);
                    par[1] = Value("@date", dateTimePicker1.Value.Date);
                    par[2] = Value("@withdrugname", true);
                    dt_rep = SqlProcExecWithQuery(competitors, "pas_SelPrice", 60, par);
                    if (dt_rep.Length > 0) 
                        if(dt_rep[0] != null)dataGridView1.DataSource = dt_rep[0].DefaultView;
                }
                else MessageBox.Show("Выберите сайт");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private sp_params Value(string name, object value)
        {
            sp_params p;
            p.name = name;
            p.value = value;
            return p;
        }

    }
}
